const connection = require('../app/database')

class MonmentService{
  async create(userId,content){
    const statement =  `INSERT INTO moment (content,user_id) VALUES(?,?);`
    const [result] = await connection.execute(statement,[content,userId])
    return result
  }

  async getMomentById(id){
    const statement = `
      SELECT
        m.id id ,m.content content,m.createAt createTime,m.updateAt updateTime,
        json_object('id',u.id,'name',u.name,'avatarUrl',u.avatar_url) users
      FROM moment m
      LEFT JOIN user u ON m.user_id = u.id
      WHERE m.id = ?;
    `
    const [result] = await connection.execute(statement,[id])
    return result[0]
  }

  async getMomentList(limit,page){
    const statement = `
      SELECT
        m.id id ,m.content content,m.createAt createTime,m.updateAt updateTime,
        json_object('id',u.id,'name',u.name) users,
        (SELECT COUNT(*) FROM comment c WHERE c.moment_id = m.id) commentCount,
        (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount
      FROM moment m
      LEFT JOIN user u ON m.user_id = u.id
      LIMIT ?, ?;
    `
    const [result] = await connection.execute(statement,[(page-1)*limit,limit])
    return result
  }
  
  async update(id,content){
    const statement = `UPDATE moment SET content = ? WHERE id = ?;`
    const [result] = await connection.execute(statement,[content,id])
    return result
  }

  async remove(id){
    const statement = `
      DELETE FROM moment WHERE id = ?;
    `
    const [result] = await connection.execute(statement,[id])
    
    return result
  }

  async hasLabel(momentId,labelId){
    const statement = `SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?;`
    const [result] = await connection.execute(statement,[momentId,labelId])
    return result[0]?true:false
  }

  async addLabel(momentId,labelId){
    const statement = `INSERT INTO moment_label (moment_id,label_id) VALUES (?,?);`
    const [result] = await connection.execute(statement,[momentId,labelId])
    return result
  }
}

module.exports = new MonmentService()